/**
* @ClassName: CandidateDaoImpl
* @Description: 
* @author YuYang
* @date 2013-6-5 上午12:46:10
*/
package com.demodis.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.demodis.dao.CandidateDaoI;
import com.demodis.domain.TblCandidate;
import com.demodis.util.DBUtil;

public class CandidateDaoImpl extends BaseDao implements CandidateDaoI {

	@Override
	public boolean save(TblCandidate t) {
		String sql = "INSERT INTO tbl_candidate(catId,userId, job,username,picture,info,describeInfo) VALUES(?, ?, ?, ?, ?, ?, ?);";
		conn = DBUtil.getConnection();
		int i = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, t.getCatId());
			ps.setInt(2, t.getUserId());
			ps.setString(3, t.getJob());
			ps.setString(4, t.getUsername());
			ps.setString(5, t.getPictureFileName());
			ps.setString(6, t.getInfoFileName());
			ps.setString(7, t.getDescribe());
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1 ? true : false;
	}

	@Override
	public boolean delete(TblCandidate t) {
		String sql = "DELETE FROM tbl_candidate WHERE canId IN(" + t.getIds() + ");";
		int i = 0;
		try {
			conn = DBUtil.getConnection();

			ps = conn.prepareStatement(sql);
			i = ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i > 0 ? true : false;
	}

	@Override
	public boolean modify(TblCandidate t) {
		String sql = "UPDATE tbl_candidate SET catId = ?, job = ?, username = ?, picture = ?, info = ?, describeInfo = ? WHERE canId = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, t.getCatId());
			ps.setString(2, t.getJob());
			ps.setString(3, t.getUsername());
			ps.setString(4, t.getPictureFileName());
			ps.setString(5, t.getInfoFileName());
			ps.setString(6, t.getDescribe());
			ps.setInt(7, t.getCanId());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1 ? true : false;
	}
	
	@Override
	public List<TblCandidate> get(TblCandidate t) {
		String sql = "SELECT a.canId, a.catId, a.username, a.poll, a.job, a.picture, b.name catName FROM tbl_candidate a, tbl_category b WHERE a.catId = b.catId ORDER BY a.poll DESC, a.catId ASC LIMIT ?, ?;";
		List<TblCandidate> list = new ArrayList<TblCandidate>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (t.getPage() - 1) * t.getRows());
			ps.setInt(2, t.getRows());
			rs = ps.executeQuery();
			while (rs.next()) {
				TblCandidate c = new TblCandidate();
				c.setCanId(rs.getInt("canId"));
				c.setCatId(rs.getInt("catId"));
				c.setCatName(rs.getString("catName"));
				c.setUsername(rs.getString("username"));
				c.setJob(rs.getString("job"));
				c.setPictureFileName(rs.getString("picture"));
				c.setPoll(rs.getInt("poll"));
				list.add(c);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public TblCandidate getCandidate(Integer canId) {
		String sql = "SELECT * FROM tbl_candidate WHERE canId = ?;";
		TblCandidate c = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, canId);
			rs = ps.executeQuery();
			if (rs.next()) {
				c = new TblCandidate();
				c.setCanId(rs.getInt("canId"));
				c.setCatId(rs.getInt("catId"));
				c.setJob(rs.getString("job"));
				c.setUsername(rs.getString("username"));
				c.setPictureFileName(rs.getString("picture"));
				c.setInfoFileName(rs.getString("info"));
				c.setDescribe(rs.getString("describeInfo"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return c;
	}

	@Override
	public List<TblCandidate> getByCatId(Integer catId, int userId) {
		String sql = "SELECT canId, catId, username, poll, picture FROM tbl_candidate WHERE userId != ? And catId = ?;";
		List<TblCandidate> list = new ArrayList<TblCandidate>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, catId);
			rs = ps.executeQuery();
			while (rs.next()) {
				TblCandidate c = new TblCandidate();
				c.setCanId(rs.getInt("canId"));
				c.setCatId(rs.getInt("catId"));
				c.setUsername(rs.getString("username"));
				c.setPoll(rs.getInt("poll"));
				c.setPictureFileName(rs.getString("picture"));
				list.add(c);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public TblCandidate getDetailedInfo(Integer canId) {
		String sql = "SELECT a.*, b.name catName FROM tbl_candidate a, tbl_category b WHERE a.catId = b.catId And canId = ?;";
		TblCandidate c = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, canId);
			rs = ps.executeQuery();
			if (rs.next()) {
				c = new TblCandidate();
				c.setCanId(rs.getInt("canId"));
				c.setCatId(rs.getInt("catId"));
				c.setJob(rs.getString("job"));
				c.setCatName(rs.getString("catName"));
				c.setUsername(rs.getString("username"));
				c.setPictureFileName(rs.getString("picture"));
				c.setInfoFileName(rs.getString("info"));
				c.setDescribe(rs.getString("describeInfo"));
				c.setPoll(rs.getInt("poll"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return c;
	}

	@Override
	public boolean doVote(int canId) {
		String sql = "UPDATE tbl_candidate SET poll = poll + 1 WHERE canId = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, canId);

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1 ? true : false;
	}

	@Override
	public List<TblCandidate> get() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int getCount() {
		int total = 0;
		String sql = "select count(*)from tbl_candidate";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return total;
	}
	
	@Override
	public boolean saveRecord(int userId) {
		String sql = "INSERT INTO tbl_doVoteUser(userId) VALUES(?);";
		conn = DBUtil.getConnection();
		int i = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1;
	}

	@Override
	public boolean isVote(int userId) {
		String sql = "SELECT count(userId) FROM tbl_doVoteUser WHERE userId = ?;";
		boolean falg = false;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			if (rs.next()) {
				falg = rs.getInt(1)==1;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return falg;
	}

	@Override
	public List<Map<String, Object>> queryVoteState(TblCandidate t) {
		String sql = "select c.groupName,d.* " +
				"from tbl_group c,(SELECT a.userId, a.userRealName, a.groupId, b.id voteId  FROM tbl_user a left join tbl_doVoteUser b on(a.userId = b.userId)) d " +
				"where c.groupId=d.groupId LIMIT ?,?;";
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (t.getPage() - 1) * t.getRows());
			ps.setInt(2, t.getRows());
			rs = ps.executeQuery();
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("groupName", rs.getString("groupName"));
				map.put("userId", rs.getString("userId"));
				map.put("userRealName", rs.getString("userRealName"));
				map.put("voteId", rs.getString("voteId"));
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public List<Map<String, Object>> queryVoteStateByGourpId(TblCandidate t) {
		String sql = "select c.groupName,d.* " +
				"from tbl_group c,(SELECT a.userId, a.userRealName, a.groupId, b.id voteId  FROM tbl_user a left join tbl_doVoteUser b on(a.userId = b.userId)) d " +
				"where c.groupId=d.groupId and c.groupId = ? LIMIT ?,?;";
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, t.getGroupId());
			ps.setInt(2, (t.getPage() - 1) * t.getRows());
			ps.setInt(3, t.getRows());
			rs = ps.executeQuery();
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("groupName", rs.getString("groupName"));
				map.put("userId", rs.getString("userId"));
				map.put("userRealName", rs.getString("userRealName"));
				map.put("voteId", rs.getString("voteId"));
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

}
